package DAO;

import javabean.Order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class OrderDAO implements IOrderDAO {
    @Override
    public boolean doCreate(Order order) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = ConnectManager.getConnection();
            String query = "INSERT INTO Orders (CustomerID, PackageID, Evaluation, Price, OrderTime, State) VALUES (?, ?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
            stmt.setString(1, order.getCustomerID());
            stmt.setInt(2, order.getPackageID());
            stmt.setString(3, order.getEvaluation());
            stmt.setDouble(4, order.getPrice());
            stmt.setTimestamp(5, new java.sql.Timestamp(order.getOrderTime().getTime()));
            stmt.setString(6, order.getState());

            int rowsInserted = stmt.executeUpdate();

            if (rowsInserted > 0) {
                rs = stmt.getGeneratedKeys();
                if (rs.next()) {
                    int generatedOrderID = rs.getInt(1);
                    order.setOrderID(generatedOrderID);
                    return true;
                }
            }

            return false;
        } finally {
            ConnectManager.closeConnection(conn, stmt, rs);
        }
    }

    @Override


    public boolean doDelete(Order order) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = ConnectManager.getConnection();
            String query = "DELETE FROM Orders WHERE OrderID = ?";
            stmt = conn.prepareStatement(query);
            stmt.setInt(1, order.getOrderID());

            int rowsDeleted = stmt.executeUpdate();
            return rowsDeleted > 0;
        } finally {
            ConnectManager.closeConnection(conn, stmt, null);
        }
    }


    @Override
    public boolean doUpdate(Order order) throws Exception {//存在外键约束问题
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = ConnectManager.getConnection();
            String query = "UPDATE Orders SET CustomerID = ?, PackageID = ?, Evaluation = ?, Price = ?, State = ? WHERE OrderID = ?";
            stmt = conn.prepareStatement(query);
            stmt.setString(1, order.getCustomerID());
            stmt.setInt(2, order.getPackageID());
            stmt.setString(3, order.getEvaluation());
            stmt.setDouble(4, order.getPrice());
            stmt.setString(5, order.getState());
            stmt.setInt(6, order.getOrderID());

            int rowsUpdated = stmt.executeUpdate();
            return rowsUpdated > 0;
        } finally {
            ConnectManager.closeConnection(conn, stmt, null);
        }
    }

    @Override
    public Order SearchOrder(Order order) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Order foundOrder = null;

        try {
            conn = ConnectManager.getConnection();
            String query = "SELECT * FROM Orders WHERE OrderID = ?";
            stmt = conn.prepareStatement(query);
            stmt.setInt(1, order.getOrderID());

            rs = stmt.executeQuery();

            if (rs.next()) {
                foundOrder = new Order();
                foundOrder.setOrderID(rs.getInt("OrderID"));
                foundOrder.setCustomerID(rs.getString("CustomerID"));
                foundOrder.setPackageID(rs.getInt("PackageID"));
                foundOrder.setEvaluation(rs.getString("Evaluation"));
                foundOrder.setPrice(rs.getDouble("Price"));
                foundOrder.setOrderTime(rs.getTimestamp("OrderTime"));
                foundOrder.setState(rs.getString("State"));
            }

            return foundOrder;
        } finally {
            ConnectManager.closeConnection(conn, stmt, rs);
        }
    }

    @Override
    public List<Order> SearchAllOrderByCID(String customerID) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Order> orderList = new ArrayList<>();

        try {
            conn = ConnectManager.getConnection();
            String query = "SELECT * FROM Orders WHERE CustomerID = ?";
            stmt = conn.prepareStatement(query);
            stmt.setString(1, customerID);

            rs = stmt.executeQuery();

            while (rs.next()) {
                Order order = new Order();
                order.setOrderID(rs.getInt("OrderID"));
                order.setCustomerID(rs.getString("CustomerID"));
                order.setPackageID(rs.getInt("PackageID"));
                order.setEvaluation(rs.getString("Evaluation"));
                order.setPrice(rs.getDouble("Price"));
                order.setOrderTime(rs.getTimestamp("OrderTime"));
                order.setState(rs.getString("State"));

                orderList.add(order);
            }

            return orderList;
        } finally {
            ConnectManager.closeConnection(conn, stmt, rs);
        }
    }
}
